package com.wontube.smr.dao.bo;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;

import com.wontube.client.extension.utils.StringUtils;
import com.wontube.client.modle.vo.PagenateInfo;
import com.wontube.core.dao.mybatis.Criteria;
import com.wontube.core.dao.mybatis.QueryExample;
import com.wontube.smr.bo.constants.SiteConstants;
import com.wontube.smr.client.model.po.TVEpisode;
import com.wontube.smr.client.model.po.TVShow;
import com.wontube.smr.client.model.vo.EpisodeItems;
import com.wontube.smr.client.model.vo.SeasonEpisodes;
import com.wontube.smr.client.model.vo.TVShowItems;
import com.wontube.smr.dao.mapper.TVEpisodeMapper;
import com.wontube.smr.dao.mapper.TVSeasonMapper;
import com.wontube.smr.dao.mapper.TVShowMapper;
import com.wontube.smr.pub.DefaultDBHelper;
import com.wontube.smr.web.client.model.bo.QueryItemParams;

/**
 * @author yuechao
 *
 */
public final class TVSelectDao {
	private TVSelectDao() {
	}
	
	public static List<Integer> getAllSeasons(String showId) {		
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		try {
			TVSeasonMapper mapper = session.getMapper(TVSeasonMapper.class);
			return mapper.selectSeasons(showId);
		} finally {
			session.close();
		}
	}
	
	public static List<SeasonEpisodes> getAllEpisodes(String showId) {
		List<SeasonEpisodes> listResult = new ArrayList<SeasonEpisodes>();
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		
		try {
			TVSeasonMapper seasonMapper = session.getMapper(TVSeasonMapper.class);
			TVEpisodeMapper epMapper = session.getMapper(TVEpisodeMapper.class);
			List<Integer> seasons = seasonMapper.selectSeasons(showId);	
			
			for (Integer seasonNum : seasons) {
				SeasonEpisodes seasonEpisodes = new SeasonEpisodes();
				seasonEpisodes.setSeasonNum(seasonNum);
				
				QueryExample ex = new QueryExample();
				ex.or().addCriterion("tv_episode.show_id = ", showId)
					.addCriterion("tv_episode.season = ", seasonNum);
				ex.setOrderByClause("tv_episode.part asc");
				
				seasonEpisodes.setEpisodes(epMapper.selectWith(ex));
				
				listResult.add(seasonEpisodes);
			}
		} finally {
			session.close();
		}
		
		return listResult;
	}
	
	
	public static TVShow getTVShow(String showId) {		
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		try {
			TVShowMapper mapper = session.getMapper(TVShowMapper.class);
			return mapper.selectByPrimaryKey(showId);
		} finally {
			session.close();
		}
	}
	
	public static TVEpisode getTVEpisode(String episodeId) {		
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		try {
			TVEpisodeMapper mapper = session.getMapper(TVEpisodeMapper.class);
			return mapper.selectWithByEpisodeId(episodeId);
		} finally {
			session.close();
		}
	}
	
	public static TVShowItems selectHuluPopularShows(PagenateInfo pageInfo) {
		return selectHuluPopularShows(null, pageInfo);
	}
	
	public static TVShowItems selectHuluPopularShows(String genreName, PagenateInfo pageInfo) {
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_HULU);
		
		ex.setOrderByClause("tv_show.sort_index asc, tv_show.version desc");
		return selectTVShowsByExample(genreName, ex, pageInfo);	
	}	
	
	public static TVShowItems searchHuluTVShows(String keyword, PagenateInfo pageInfo) {			
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website = ", SiteConstants.WEBSITE_HULU)
			.addCriterion(getTVShowLikeString(keyword));
				
		ex.setOrderByClause("tv_show.sort_index asc, tv_show.version desc");

		return selectTVShowsByExample(null, ex, pageInfo);
	}
	
	public static TVShowItems searchCrunchyrollAnimes(String keyword, PagenateInfo pageInfo){
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_CRUNCHYROLL)
			.addCriterion("tv_show.category=", TVShow.CATEGORY_ANIME)
			.addCriterion(getTVShowLikeString(keyword));
				
		ex.setOrderByClause("tv_show.sort_index asc, tv_show.version desc");

		return selectTVShowsByExample(null, ex, pageInfo);
	}
	
	public static EpisodeItems searchCrunchyrollAnimeEpisodes(String keyword, PagenateInfo pageInfo) {
		StringBuilder sb = new StringBuilder();
		keyword = keyword.replaceAll("[']", "''");
		sb.append("(tv_show.name ilike '%" + keyword + "%'");
		sb.append(" or tv_episode.episode_name ilike '%" + keyword + "%')");		
		
		QueryExample ex = new QueryExample();		
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_CRUNCHYROLL)
			.addCriterion(sb.toString());
		ex.setOrderByClause("tv_show.sort_index asc, tv_episode.part desc");
		
		return selectEpisodesByExample(null, ex, pageInfo);
	}
	
	public static int getHuluTVShowCount(String keyword) {				
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website = ", SiteConstants.WEBSITE_HULU)
			.addCriterion(getTVShowLikeString(keyword));
		
		return getTVShowCount(ex);
	}
	
	public static int getCrunchyrollAnimeCount(String keyword){
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website = ", SiteConstants.WEBSITE_CRUNCHYROLL)
			.addCriterion("tv_show.category=", TVShow.CATEGORY_ANIME)
			.addCriterion(getTVShowLikeString(keyword));
		
		return getTVShowCount(ex);
	}
	
	private static String getTVShowLikeString(String keyword){
		StringBuilder sb = new StringBuilder();
		keyword = keyword.replaceAll("[']", "''");
		sb.append("(tv_show.name ilike '%" + keyword + "%'");
		sb.append(" or tv_show.actors ilike '%" + keyword + "%'");
		sb.append(" or tv_show.director ilike '%" + keyword + "%')");	
		return sb.toString();
	}
	
	public static List<TVEpisode> getEpisodeList(String showId, int seasonNum) {
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_episode.show_id = ", showId)
			.addCriterion("tv_episode.season = ", seasonNum);
		ex.setOrderByClause("tv_episode.part asc");

		SqlSession session = DefaultDBHelper.getSqlSession(true);
		try {
			TVEpisodeMapper mapper = session.getMapper(TVEpisodeMapper.class);
			List<TVEpisode> tvEpisodes = mapper.selectWith(ex);
			
			return tvEpisodes;
		} finally {
			session.close();
		}
	}
	
	public static TVShowItems selectHuluRecentShows(PagenateInfo pageInfo) {
		return selectHuluRecentShows(null, pageInfo);
	}
	
	public static TVShowItems selectHuluRecentShows(String genreName, PagenateInfo pageInfo) {
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_HULU);
		ex.setOrderByClause("recent_show.sort_index asc, recent_show.version desc");
		return selectRecentShowsByExample(genreName, ex, pageInfo);
	}	
	
	public static EpisodeItems selectHuluPopularEpisodes(PagenateInfo pageInfo) {
		return selectHuluPopularEpisodes(null, pageInfo);
	}
	
	public static EpisodeItems selectHuluPopularEpisodes(String genreName, PagenateInfo pageInfo) {
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_episode.website=", SiteConstants.WEBSITE_HULU);
		ex.setOrderByClause("popular_episode.sort_index asc, popular_episode.version desc");
		return selectPopularEpisodesByExample(genreName, ex, pageInfo);
	}
	
	public static EpisodeItems selectHuluRecentEpisodes(String genreName, PagenateInfo pageInfo) {
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_episode.website=", SiteConstants.WEBSITE_HULU);
		ex.setOrderByClause("recent_episode.sort_index asc, recent_episode.version desc");

		return selectRecentEpisodesByExample(genreName, ex, pageInfo);
	}
	
	public static EpisodeItems searchHuluEpisodes(String keyword, PagenateInfo pageInfo) {
		StringBuilder sb = new StringBuilder();
		keyword = keyword.replaceAll("[']", "''");
		sb.append("(tv_show.name ilike '%" + keyword + "%'");
		sb.append(" or tv_episode.episode_name ilike '%" + keyword + "%')");		
		
		QueryExample ex = new QueryExample();		
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_HULU)
			.addCriterion(sb.toString());
		ex.setOrderByClause("tv_show.sort_index asc, tv_episode.part desc");
		
		return selectEpisodesByExample(null, ex, pageInfo);
	}
	
	public static TVShowItems selectHuluTopRatedTVShows(String genreName, PagenateInfo pageInfo) {
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_HULU);
		ex.setOrderByClause("tv_show.score desc");
		return selectTVShowsByExample(genreName, ex, pageInfo);
	}
	
	public static EpisodeItems selectHuluTopRatedEpisodes(String genreName, PagenateInfo pageInfo) {
		QueryExample ex = new QueryExample();
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_HULU);
		ex.setOrderByClause("tv_episode.score desc");
		return selectEpisodesByExample(genreName, ex, pageInfo);
	}
	
	public static TVShowItems selectHuluTVShows(QueryItemParams params, PagenateInfo pageInfo){	
		QueryExample ex = new QueryExample();
		Criteria cri = ex.or();
		cri.addCriterion("tv_show.website=", SiteConstants.WEBSITE_HULU)
			.addCriterion("tv_show.title_initial=", params.getInitial());
		
		Integer decade = params.getDecade();
		if(decade != null){
			Calendar calendar = Calendar.getInstance();
			calendar.clear();
			calendar.set(decade, 0, 1);
			cri.addCriterion("tv_show.air_begin_date >=", calendar.getTime());
			calendar.set(decade + 10, 0, 1);
			cri.addCriterion("tv_show.air_begin_date <", calendar.getTime());
		}
		
		if("time".equals(params.getSort())){
			ex.setOrderByClause("tv_show.air_begin_date desc");
		}else{
			ex.setOrderByClause("tv_show.sort_index asc");
		}
		
		return selectTVShowsByExample(params.getGenre(), ex, pageInfo);
	}
	
	//======drama====
	public static TVShowItems selectCrunchyrollCountryDramas(String category,PagenateInfo pageInfo){
		QueryExample ex = new QueryExample();		
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_CRUNCHYROLL)
			.addCriterion("tv_show.category=", category);
		
		ex.setOrderByClause("tv_show.sort_index asc");
		return selectTVShowsByExample(ex, pageInfo);
	}
	
	public static TVShowItems selectCrunchyrollDramas(PagenateInfo pageInfo){
		QueryExample ex = new QueryExample();		
		ex.or().addCriterion("tv_show.website=", SiteConstants.WEBSITE_CRUNCHYROLL)
			.addCriterion("tv_show.category=", TVShow.CATEGORY_DRAMA);
		
		ex.setOrderByClause("tv_show.sort_index asc");
		return selectTVShowsByExample(ex, pageInfo);
	}
	

	private static int getTVShowCount(QueryExample ex) {
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		try {
			TVShowMapper mapper = session.getMapper(TVShowMapper.class);
			return mapper.countByExample(ex);
		} finally {
			session.close();
		}
	}
	
	private static TVShowItems selectTVShowsByExample(QueryExample ex, PagenateInfo pageInfo) {
		return selectTVShowsByExample(null, ex, pageInfo);
	}
	
	public static TVShowItems selectTVShowsByExample(String genreName, QueryExample ex, PagenateInfo pageInfo) {
		TVShowItems items = new TVShowItems(pageInfo);		
		RowBounds rowBounds = new RowBounds(pageInfo.getOffset(), pageInfo.getLimit());
		
		SqlSession session = DefaultDBHelper.getSqlSession(true);		
		try {
			TVShowMapper mapper = session.getMapper(TVShowMapper.class);
			
			if(StringUtils.isNullOrEmpty(genreName)){
				items.setItems(mapper.selectByExample(ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countByExample(ex));
				}
			}else{
				items.setItems(mapper.selectWithByGenre(genreName, ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWithByGenre(genreName, ex));
				}
			}			
		} finally {
			session.close();
		}
		
		return items;
	}
	
	public static TVShowItems selectRecentShowsByExample(String genreName, QueryExample ex, PagenateInfo pageInfo) {
		TVShowItems items = new TVShowItems(pageInfo);		
		
		RowBounds rowBounds = new RowBounds(pageInfo.getOffset(), pageInfo.getLimit());		
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		
		try {
			TVShowMapper mapper = session.getMapper(TVShowMapper.class);
			
			if(StringUtils.isNullOrEmpty(genreName)){
				items.setItems(mapper.selectWithRecent(ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWithRecent(ex));
				}
			}else{
				items.setItems(mapper.selectWithRecentByGenre(genreName, ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWithRecentByGenre(genreName, ex));
				}
			}			
		} finally {
			session.close();
		}
		
		return items;
	}
	
	public static EpisodeItems selectRecentEpisodesByExample(String genreName, QueryExample ex, PagenateInfo pageInfo) {
		EpisodeItems items = new EpisodeItems(pageInfo);		
		RowBounds rowBounds = new RowBounds(pageInfo.getOffset(), pageInfo.getLimit());
		
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		try {
			TVEpisodeMapper mapper = session.getMapper(TVEpisodeMapper.class);
			
			if(StringUtils.isNullOrEmpty(genreName)){
				items.setItems(mapper.selectWithRecent(ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWithRecent(ex));
				}
			}else{
				items.setItems(mapper.selectWithRecentByGenre(genreName, ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWithRecentByGenre(genreName, ex));
				}
			}
			
		} finally {
			session.close();
		}
		
		return items;
	}
	
	
	public static EpisodeItems selectEpisodesByExample(String genreName, QueryExample ex, PagenateInfo pageInfo) {
		EpisodeItems items = new EpisodeItems(pageInfo);				
		RowBounds rowBounds = new RowBounds(pageInfo.getOffset(), pageInfo.getLimit());
		
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		
		try {			
			TVEpisodeMapper mapper = session.getMapper(TVEpisodeMapper.class);
			if(StringUtils.isNullOrEmpty(genreName)){
				items.setItems(mapper.selectWithPage(ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWith(ex));
				}
			}else{
				items.setItems(mapper.selectWithPopularByGenre(genreName, ex, rowBounds));				
			}			
			
		} finally {
			session.close();
		}
		
		return items;
	}
	
	private static EpisodeItems selectPopularEpisodesByExample(String genreName, QueryExample ex, PagenateInfo pageInfo) {
		EpisodeItems items = new EpisodeItems(pageInfo);		
		RowBounds rowBounds = new RowBounds(pageInfo.getOffset(), pageInfo.getLimit());	
		
		SqlSession session = DefaultDBHelper.getSqlSession(true);
		try {
			TVEpisodeMapper mapper = session.getMapper(TVEpisodeMapper.class);
			if(StringUtils.isNullOrEmpty(genreName)){
				items.setItems(mapper.selectWithPopular(ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWithPopular(ex));
				}
			}else{
				items.setItems( mapper.selectWithPopularByGenre(genreName, ex, rowBounds));
				if(pageInfo.getPageNo() == 1){
					items.setTotalCount(mapper.countWithPopularByGenre(genreName, ex));
				}
			}
		} finally {
			session.close();
		}
		
		return items;
	}	
}
